-- @owner: @songjing20
-- @date: 2023-1-16
-- @testpoint: desc/describe分析merge into语句

--step1:创建两张表并插入数据;expect:成功
drop table if exists t_describe_0073_01, t_describe_0073_02 cascade;
create table t_describe_0073_01(product_id integer, product_name varchar2(60), category varchar2(60));
insert into t_describe_0073_01 values(1501, 'vivitar 35mm', 'electrncs'), (1502, 'olympus is50', 'electrncs');
insert into t_describe_0073_01 values(1600, 'play gym', 'toys'), (1601, 'lamaze', 'toys'), (1666, 'harry potter', 'dvd');

create table t_describe_0073_02(product_id integer, product_name varchar2(60), category varchar2(60)) comment = 'new_product';
insert into t_describe_0073_02 values(1502, 'olympus camera', 'electrncs'), (1601, 'lamaze', 'toys');
insert into t_describe_0073_02 values(1666, 'harry potter', 'toys'), (1700, 'wait interface', 'books');

--step2:分析merge into执行计划;expect:打印执行计划，结果与explain一致
begin;/
explain analyze merge into t_describe_0073_01 p using t_describe_0073_02 np on (p.product_id = np.product_id)
when matched then  
  update set p.product_name = np.product_name, p.category = np.category where p.product_name != 'play gym'  
when not matched then  
  insert values (np.product_id, np.product_name, np.category) where np.category = 'books';
rollback;

begin;/
desc analyze merge into t_describe_0073_01 p using t_describe_0073_02 np on (p.product_id = np.product_id)
when matched then  
  update set p.product_name = np.product_name, p.category = np.category where p.product_name != 'play gym'  
when not matched then  
  insert values (np.product_id, np.product_name, np.category) where np.category = 'books';
rollback;

begin;/
describe analyze merge into t_describe_0073_01 p using t_describe_0073_02 np on (p.product_id = np.product_id)
when matched then  
  update set p.product_name = np.product_name, p.category = np.category where p.product_name != 'play gym'  
when not matched then  
  insert values (np.product_id, np.product_name, np.category) where np.category = 'books';
commit;

--step3:查询更新后的结果;expect:6条数据
select * from t_describe_0073_01 order by product_id;

--step4:清理环境;expect:成功
drop table if exists t_describe_0073_01, t_describe_0073_02 cascade;